package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Iterables;
import com.google.common.collect.Maps;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.Lsh活动推送Model;
import com.iris.live.services.data.models.用户Model;
import com.iris.live.services.data.repositories.Lsh活动推送Repository;
import com.iris.live.services.data.repositories.MarketRepository;
import com.iris.live.services.data.repositories.用户Repository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.MarketService;
import com.iris.live.services.services.impl.ApiForLiveServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xin on 2016/8/9.
 */
@Repository
@Transactional//(readOnly = true)
public class MarketRepositoryImpl extends BaseRepository implements MarketRepository {
    @Autowired
    private Lsh活动推送Repository lsh活动推送Repository;

    @Autowired
    private 用户Repository repository用户;

    @Override
    public PageData getActionList(int pageSize, int pageIndex, String permit,
                                  String code, String name, String level, String type, Date sDate, Date eDate, String actionCode, String starter) {
        String sql = "SELECT \n" +
                "    a.活动编号, a.创建时间 as 创建日期,\n" +
                "    a.活动代码,\n" +
                "    a.活动名称,\n" +
                "    b.名称 as 主导与发起,\n" +
                "    c.活动类型,\n" +
                "    a.活动目的,\n" +
                "    group_concat(d.车型编号) as 主推车系,\n" +
                "    a.开始日期 as 活动开始日期,\n" +
                "    a.结束日期 as 活动结束日期,\n" +
                "    IF(a.结束日期 IS NULL OR a.启用状态 = '停用',a.启用状态, IF(date_format(a.结束日期, '%Y-%m-%d') < date_format(now(), '%Y-%m-%d'),'停用','启用')) 状态 \n" +
                "FROM\n" +
                "    LSH活动表 a\n" +
                "       left JOIN\n" +
                "    LSH活动发起设置 b ON a.活动发起编号 = b.活动发起编号\n" +
                "       left JOIN\n" +
                "    LSH活动类型 c ON a.活动类型编号 = c.活动类型编号\n" +
                "       left JOIN\n" +
                "    LSH活动车型表 d ON a.活动编号 = d.活动编号\n" +
                "       left JOIN\n" +
                "    LSH车型 e ON d.车型编号 = e.车型编号\n" +
                "where a.启用状态 <> '已删除' \n" +
                " and a.许可=:permit ";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(code)) {
            sql += "and a.活动代码 like :code \n";
            params.put("code", "%" + code + "%");
        }

        if (!Strings.isNullOrEmpty(name)) {
            sql += "and a.活动名称 like :name \n";
            params.put("name", "%" + name + "%");
        }

        if (!Strings.isNullOrEmpty(level)) {
            sql += "and a.活动级别 = :level \n";
            params.put("level", level);
        }

        if (!Strings.isNullOrEmpty(type)) {
            sql += "and c.活动类型 = :type \n";
            params.put("type", type);
        }
        /**
         * 市场活动日期查询
         */
        if(sDate != null && !"".equals(sDate.toString())&&eDate != null && !"".equals(eDate.toString())){
            sql+="AND (" +
                    "    (" +
                    "      a.开始日期 <= :startDate " +
                    "      AND a.结束日期 >= :endDate  " +
                    "    ) " +
                    "    OR (" +
                    "      a.开始日期 <= :startDate " +
                    "      AND a.结束日期 >= :startDate  " +
                    "    ) " +
                    "    OR (" +
                    "      a.开始日期 <= :endDate  " +
                    "      AND a.结束日期 >= :endDate" +
                    "    ) " +
                    "    OR (" +
                    "      a.开始日期 >= :startDate " +
                    "      AND a.结束日期 <= :endDate " +
                    "    )" +
                    "  ) ";
            params.put("startDate", sDate);
            params.put("endDate", eDate);

        }else {
            if(sDate != null && !"".equals(sDate.toString())){
                sql += " and (a.开始日期 >= :startDate  or  a.结束日期>=:startDate ) \n";
                params.put("startDate", sDate);

            }else if(eDate != null && !"".equals(eDate.toString())){
                sql += " and (a.结束日期 <= :endDate or a.开始日期<= :endDate  )   \n";
                params.put("endDate", eDate);
            }
        }

        if (!Strings.isNullOrEmpty(actionCode)) {
            sql += "and a.x = :actionCode \n";
            params.put("actionCode", actionCode);
        }

        if (!Strings.isNullOrEmpty(starter)) {
            sql += "and b.名称 = :starter \n";
            params.put("starter", starter);
        }
        sql += " group by a.活动编号 ";
        sql += "order by a.更新时间 desc";
        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public Object getActionInfoByID(int id) {
        String sql = "SELECT " +
                " a.活动编号, " +
                " a.活动代码, " +
                " a.活动名称, " +
                " a.`启用状态`, " +
                " c.活动类型, " +
                " b.名称 AS 主导与发起, " +
                " a.开始日期 AS 活动开始日期, " +
                " a.结束日期 AS 活动结束日期, " +
                " a.活动目的, " +
                " group_concat(d.车型编号) AS 主推车系, " +
                " a.活动内容 AS 活动内容, " +
                " a.文件路径 AS 活动政策, " +
                " a.活动话术 AS 活动话术, " +
                " a.活动宣传页路径 AS 宣传页, " +
                " a.创建时间, " +
                " a.更新时间, " +
                " a.更新账号, " +
                " IFNULL(f.`导入日期`,'') 导入日期 " +
                "FROM " +
                " LSH活动表 a " +
                "LEFT JOIN LSH活动发起设置 b ON a.活动发起编号 = b.活动发起编号 " +
                "LEFT JOIN LSH活动类型 c ON a.活动类型编号 = c.活动类型编号 " +
                "LEFT JOIN LSH活动车型表 d ON a.活动编号 = d.活动编号 " +
                "LEFT JOIN LSH车型 e ON d.车型编号 = e.车型编号 " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  MAX(a.`导入日期`) 导入日期, " +
                "  `活动编号` " +
                " FROM " +
                "  `lsh导入名单` a " +
                " WHERE " +
                "  ! ISNULL(a.`活动编号`) " +
                " AND ( " +
                "  a.`导入状态` = '0' " +
                "  OR a.`导入状态` = '1' " +
                " ) " +
                " GROUP BY " +
                "  a.`活动编号` " +
                ") f ON a.`活动编号` = f.`活动编号` " +
                "WHERE " +
                " a.活动编号 = :id ";
        Map params = Maps.newHashMap();
        params.put("id", id);
        List data = this.getList(sql, params);
        List<Lsh活动推送Model> modelList = lsh活动推送Repository.findByCode(id);
        //遍历List,将sb装入map
        for (Object obj : data) {
            Map map = (Map) obj;
            StringBuilder sb = new StringBuilder();
            if (modelList.size() > 0) {
                for (int i = 0; i < modelList.size(); i++) {
                    String pushType = modelList.get(i).get推送类型();//获取活动推送类型
                    if (i == 0) {
                        sb.append(pushType);
                    } else {
                        sb.append("," + pushType);
                    }

                }
            }
            map.put("推送类型", sb.toString());
        }
        return Iterables.getFirst(data, null);
    }

    @Override
    public List getActionPosts(int actionID) {
        String sql = "select * from LSH活动推送岗位 where 活动编号 = :actionID";
        Map params = Maps.newHashMap();
        params.put("actionID", actionID);

        return this.getList(sql, params);
    }

    @Override
    public List getAppointment(String actionID) {
        String sql = "select a.顾客姓名,a.电话号码,b.级别,b.车型,b.品牌,b.来源,c.销售顾问,a.客户级别 from LSH导入名单 a\n" +
                "join LSH车型 b on a.车型编号 = b.车型编号\n" +
                "join 预约信息表 c on a.预约编号 = c.预约编号\n" +
                "where  a.活动编号 = :actionID";
        Map params = Maps.newHashMap();
        params.put("actionID", actionID);

        return this.getList(sql, params);
    }

    @Override
    public List getMarketSetType(String permission, String channel) {
        String sql = "select distinct 来源分类 " +
                "from LSH市场设置 " +
                "where (许可 = :permission or 许可= 'LSH') " +
                "and 渠道 = :channel ";
        Map params = Maps.newHashMap();
        params.put("permission", permission);
        params.put("channel", channel);

        return this.getList(sql, params);
    }

    @Override
    public List getMarketSetSource(String permission, String channel, String type) {
        String sql = "select distinct 来源 ,启用状态 " +
                "from LSH市场设置 " +
                "where (许可 = :permission or 许可 = 'LSH')" +
                "and 渠道 = :channel " +
                "and 来源分类 = :type";
        Map params = Maps.newHashMap();
        params.put("permission", permission);
        params.put("channel", channel);
        params.put("type", type);
        return this.getList(sql, params);
    }

    @Override
    public List getActionByCar(String permission,  List <String> list ) {
        String sql = "select a.活动编号,a.活动名称,a.活动话术 from LSH活动表 a\n" +
                "left join LSH活动车型表 b on a.活动编号 = b.活动编号\n" +
                "where b.车型编号  in ";
        String car ="";
        for(int a=0;a<list.size();a++){
            if (a==0){
                car=car+"'"+list.get(a)+"'";}
            else {
                car=car+", "+"'"+list.get(a)+"'";
            }
        }
        car ="("+car+")";
        sql = sql + car + "and a.许可 =:permission  and  a.`启用状态`='启用'  and   a.`结束日期`  >=CURRENT_DATE()   group by a.活动编号";
        Map params = Maps.newHashMap();
        params.put("permission", permission);
        return this.getList(sql, params);
    }

    @Override
    public PageData getCustomerRecords(String permit, MarketService.GetCustomerRecords request) {
        String sql = "select  " +
                "   e.来源分类, " +
                "	a.导入编号, " +
                "	a.所属销售顾问, " +
                "	a.顾客编号, " +
                "   a.更新时间, " +
                "	a.渠道, " +
                "	b.车型, " +
                "	a.客户级别, " +
                "	a.来源, " +
                "	b.级别, " +
                "    a.顾客姓名, " +
                "    a.电话号码, " +
                "    a.预计购车日期, " +
                "    a.保有品牌 as 现有车型, " +
                "    a.所属CDO, " +
                "    a.是否线索, " +
                "    a.claim_state, " +
                "    f.活动名称 as 市场活动名称, " +
                "    a.下次跟进日期 as 下次跟进时间, " +
                "    case " +
                "		when d.模式 = '跟进' and d.跟进结果 <> '新车战败' then '已跟进' " +
                "        when d.模式 = '邀约' and d.跟进结果 <> '新车战败' then '已邀约' " +
                "	end as 跟进_邀约状态, " +
                "    c.开始时间 as 预约到店日期,  " +
                "   a.更新时间 建档时间, " +
                "  (select 人员姓名 from 用户 where 用户名 = a.更新账号) 创建人 " +
                "from LSH导入名单 a " +
                "left join LSH车型 b on a.车型编号 = b.车型编号 " +
                "left join LSH待办事项 c on c.附属编号 = a.导入编号 and c.附属编号类型 = '线索' " +
                "left join 跟进记录表 d on a.电话号码 = d.电话号码 " +
                "left join LSH市场设置 e on a.市场编号 = e.市场编号 " +
                "left join LSH活动表 f on a.活动编号 = f.活动编号 " +
                "where (a.所属销售顾问  IS NULL OR a.所属销售顾问 ='' ) AND (a.渠道 IN ('主动集客','呼入','网络','再购') OR (a.info_source = 'CRM')) " +
                " AND a.是否有效线索='是' " +
                " AND (a.战败去向 IS NULL OR a.战败去向='') " +
               " AND DATE_FORMAT(a.`线索创建日期`,'%Y-%m') > DATE_FORMAT(DATE_sub(CURDATE(),INTERVAL 3 MONTH),'%Y-%m') \n" +
               " AND DATE_FORMAT(a.`线索创建日期`,'%Y-%m') < DATE_FORMAT(DATE_sub(CURDATE(),INTERVAL -1 MONTH),'%Y-%m') \t" +
                " AND a.许可=:permit \n";
        Map params = Maps.newHashMap();
        params.put("permit", permit);

        //查询用户表，看是否是"客户发展经理"
        用户Model model用户 = repository用户.findOneByEmail(permit, request.belongCDO);
        if (model用户 == null) {
            sql += " and (a.所属CDO =:email or a.所属CDO IS NULL) ";
            params.put("email", request.belongCDO);
        } else {
            if (!"客户发展经理".equals(model用户.get角色())) {
                sql += " and (a.所属CDO =:email or a.所属CDO IS NULL) ";
                params.put("email", request.belongCDO);
            }
        }

        if (!Strings.isNullOrEmpty(request.type)) {
            sql += "and e.来源分类 = :type ";
            params.put("type", request.type);
        }
        if (!Strings.isNullOrEmpty(request.phone)) {
            sql += "and a.电话号码 like :phone \n";
            params.put("phone", "%" + request.phone + "%");
        }
        if (!Strings.isNullOrEmpty(request.name)) {
            sql += "and a.顾客姓名 like :name \n";
            params.put("name", "%" + request.name + "%");
        }
        if (!Strings.isNullOrEmpty(request.sex)) {
            sql += "and a.顾客性别 = :sex \n";
            params.put("sex", request.sex);
        }
        if (!Strings.isNullOrEmpty(request.channel)) {
            sql += "and a.渠道 = :channel \n";
            params.put("channel", request.channel);
        }
        if (!Strings.isNullOrEmpty(request.source)) {
            sql += "and a.来源 = :source \n";
            params.put("source", request.source);
        }
        if (!Strings.isNullOrEmpty(request.car)) {
            //sql += "and a.意向车型A = :car \n";
            sql += "and exists (select 1 from LSH车型 car where car.车型 like :car and a.车型编号 = car.车型编号) \n";
            params.put("car", "%" + request.car + "%");
        }
        if (!Strings.isNullOrEmpty(request.level)) {
            sql += "and a.客户级别 = :level \n";
            params.put("level", request.level);
        }
        if (!Strings.isNullOrEmpty(request.marketActiveName)) {
            sql += " AND f.活动名称 like :marketActiveName ";
            params.put("marketActiveName", "%" + request.marketActiveName + "%");
        }
        sql += "group by a.电话号码 order by a.claim_state DESC, a.更新时间 desc ";
//        if (!Strings.isNullOrEmpty(request.status)){
//            sql += "and d.跟进状态 = :status \n";
//            params.put("status",request.status);
//        }
        return this.getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public Map getCustomerRecordsInfo(MarketService.GetCustomerRecords request) {
        String sql = "select * from 顾客记录汇总 a \n" +
                "where 1 = 1\n";
        Map params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(request.phone)) {
            sql += "and a.电话号码 = :phone \n";
            params.put("phone", request.phone);
        }
        if (!Strings.isNullOrEmpty(request.name)) {
            sql += "and a.顾客姓名 = :name \n";
            params.put("name", request.name);
        }
        List list = this.getList(sql, params);
        return list.size() > 0 ? (Map) list.get(0) : null;
    }

    @Override
    public Object findInputListByPhone(String phone) {
        String sql = "select * from LSH导入名单 a \n" +
                "where 电话号码 = :phone \n" +
                "order by 更新时间 desc limit 1 ";
        Map params = Maps.newHashMap();
        params.put("phone", phone);
        List list = this.getList(sql, params);
        return Iterables.getFirst(list, null);
    }

    @Override
    public int getTaskCompleteCount(String permission, String customer) {
        String sql = "select count(1) from LSH导入名单 a\n" +
                "join 预约信息表 b on a.预约编号 = b.预约编号 and b.当前状态 = '已确认'\n" +
                "where a.许可 = :permission and b.销售顾问 = :customer and (TO_DAYS(NOW()) - TO_DAYS(b.更新时间)) = 0 \n";
        Map params = new HashMap();
        params.put("permission", permission);
        params.put("customer", customer);
        return this.getCount(sql, params);
    }

    @Override
    public List getSaleListByCustomer(int customerID) {
        String sql = "select 销售顾问 from 顾客订单记录 where 顾客编号 = :customerID order by 更新时间 desc";
        Map params = new HashMap();
        params.put("customerID", customerID);
        return this.getList(sql, params);
    }

    @Override
    public List getPassengerFlowLogByCustomer(int customerID) {
        String sql = "select * from 顾客进店记录表 where 顾客编号 = :customerID order by 更新时间 desc";
        Map params = new HashMap();
        params.put("customerID", customerID);
        return this.getList(sql, params);
    }

    @Override
    public List getActionIDList(int id) {
        String sql = "select 活动编号 from LSH导入名单联系附属 where 联系记录编号 = :id";
        Map params = new HashMap();
        params.put("id", id);
        return this.getList(sql, params);
    }

    @Override
    public Object getOrderStatus(int customer) {
        String sql = "select 当前状态 from 报价单表 where 顾客编号 = :customer order by 更新时间 desc limit 1";
        Map params = new HashMap();
        params.put("customer", customer);
        List list = this.getList(sql, params);
        return Iterables.getFirst(list, null);
    }

    @Override
    public List getOrderStatusList(int customer) {
        String sql = "select 当前状态 from 报价单表 where 顾客编号 = :customer";
        Map params = new HashMap();
        params.put("customer", customer);
        return this.getList(sql, params);
    }

    @Override
    public Map getCustomerIDFromRecords(String customer, String sale) {
        String sql = "select 顾客编号 from 顾客记录汇总 where 顾客姓名 = :customer and 销售顾问 = :sale limit 1";
        Map params = new HashMap();
        params.put("customer", customer);
        params.put("sale", sale);
        List list = this.getList(sql, params);
        return list.size() > 0 ? (Map) list.get(0) : null;
    }

    @Override
    public List getCompetingList() {
        String sql = "select 品牌,车型 from LSH对比车型 group by 品牌,车型";

        return this.getList(sql);
    }

    @Override
    public List getCompetingListByBrand(String brand) {
        String sql = "select 对比车型编号,车型 from LSH对比车型 where 品牌 = :brand";
        Map params = new HashMap();
        params.put("brand", brand);
        return this.getList(sql, params);
    }

    @Override
    public PageData getGetActKPIList(MarketService.GetGetActKPIListReq req) {
        Map map = Maps.newHashMap();
        String sql = "select * from lsh活动kpi where  ";
        String[] arr = ApiForLiveServiceImpl.getStringArr(req.permit);
        for (int i = 0; i < arr.length; i++) {
            if (i == 0) {
                sql += " 许可=:permit";
                map.put("permit", arr[i]);
            } else {
                sql += " OR 许可=:permit1";
                map.put("permit1", arr[i]);
            }
        }
        sql += " order by 更新时间 desc";
        return this.getPage(sql, map, req.getPageSize(), req.getPageIndex());
    }

    @Override
    public List getActAttachKPIList(MarketService.GetGetActKPIListReq req) {
        String sql = "";
        if (req.活动编号 == null) {
            sql = "SELECT a.`KPI编号`, b.`选择状态`, '未选' AS 活动内选择状态,\n" +
                    "a.许可,a.KPI定义,a.启用状态,a.KPI,a.KPI目的,a.KPI公式,a.KPI备注 " +
                    "FROM `lsh活动kpi` a \n" +
                    "JOIN `lsh活动类型附属` b ON a.`KPI编号` = b.`KPI编号` \n" +
                    "WHERE b.`活动类型编号` =:actCode and (a.`许可`='LSH' OR a.`许可`=:permit)  ";
        } else {
            sql = "SELECT a.`KPI编号`, b.`选择状态`, IF(c.`KPI编号` IS NULL,'未选','已选') AS 活动内选择状态,  \n" +
                    "                                    a.许可,a.KPI定义,a.启用状态,a.KPI,a.KPI目的,a.KPI公式,a.KPI备注,c.`活动目标值`\n" +
                    "                                    FROM `lsh活动kpi` a   \n" +
                    "                                    JOIN `lsh活动类型附属` b ON a.`KPI编号` = b.`KPI编号`   \n" +
                    "                                    LEFT JOIN `lsh活动目标` c ON c.`KPI编号` = a.`KPI编号`  and c.`活动编号`=:actNum \n" +
                    "                                    WHERE b.`活动类型编号` =:actCode and (a.`许可`='LSH' OR a.`许可`=:permit)  \n" +
                    "                    UNION ALL  \n" +
                    "                    select m.`KPI编号`,'可选' AS 选择状态,'已选' AS 活动内选择状态,m.`许可`,m.`KPI定义`,m.`启用状态`,m.KPI,m.`KPI目的`,m.`KPI公式`,m.`KPI备注`,n.`活动目标值`  \n" +
                    "                    from `lsh活动kpi` m  JOIN (  \n" +
                    "                      select `KPI编号`,`活动目标值` from lsh活动目标 where `活动编号`=:actNum and `KPI编号` NOT IN (  \n" +
                    "                                    SELECT a.KPI编号\n" +
                    "                                    FROM `lsh活动kpi` a   \n" +
                    "                                    JOIN `lsh活动类型附属` b ON a.`KPI编号` = b.`KPI编号`   \n" +
                    "                                    LEFT JOIN `lsh活动目标` c ON c.`KPI编号` = a.`KPI编号`   \n" +
                    "                                    WHERE b.`活动类型编号` =:actCode and (a.`许可`='LSH' OR a.`许可`=:permit)  \n" +
                    "                      \n" +
                    "                    \t\t)  \n" +
                    "                    ) n ON m.`KPI编号`=n.`KPI编号`";

        }

        Map map = Maps.newHashMap();
        map.put("actCode", req.活动类型编号);
        map.put("permit", req.许可);
        if (req.活动编号 != null) {
            map.put("actNum", req.活动编号);
        }
        List list = this.getList(sql, map);
        return list;
    }

    @Override
    public PageData getActRecommendKPIList(MarketService.GetGetActKPIListReq req) {
        String sql = "SELECT e.`活动类型编号`, f.`活动类型`, e.`可选`, e.`必选`, e.`更新时间`, f.`启用状态`, f.`备注`, \n" +
                "( SELECT MAX(g.`更新账号`) FROM `lsh活动类型附属` g \n" +
                "WHERE g.`更新时间` = e.`更新时间` AND g.`活动类型编号` = e.`活动类型编号` ) AS 更新账号 \n" +
                "FROM ( SELECT d.`活动类型编号`, MAX( CASE d.`选择状态` WHEN '必选' THEN d.kpi END ) AS '必选', \n" +
                "MAX( CASE d.`选择状态` WHEN '可选' THEN d.kpi END ) AS '可选', d.`更新时间` \n" +
                "FROM ( SELECT c.`活动类型编号`, c.`选择状态`, GROUP_CONCAT(c.kpi编号) AS kpi编号, \n" +
                "GROUP_CONCAT(c.kpi) AS kpi, MAX(c.`更新时间`) AS '更新时间' \n" +
                "FROM ( SELECT a.`KPI编号`, b.KPI, a.`活动类型编号`, a.`选择状态`, a.`更新时间` \n" +
                "FROM `lsh活动类型附属` a JOIN ( SELECT kpi编号, KPI FROM lsh活动kpi WHERE `许可` =:permit ) b \n" +
                "ON a.`kpi编号` = b.`KPI编号` ) c GROUP BY c.`活动类型编号`, c.`选择状态` ) d GROUP BY d.`活动类型编号` ) e \n" +
                "LEFT JOIN `lsh活动类型` f ON e.`活动类型编号` = f.`活动类型编号` ORDER BY e.`更新时间` DESC ";
        Map map = Maps.newHashMap();
        map.put("permit", req.许可);
        return this.getPage(sql, map, req.getPageSize(), req.getPageIndex());
    }

    @Override
    public List find邀约客户数(MarketService.GetActKPIReq req) {
        String sql = "select * FROM `LSH导入名单`\n" +
                "\tWHERE `导入状态` = '0' AND `是否线索` = '是' AND `许可` =:permit AND `活动编号` =:actCode";
        Map map = Maps.newHashMap();
        map.put("permit", req.permit);
        map.put("actCode", req.actCode);
        return this.getList(sql, map);
    }

    private Map getKpiPramsMap(MarketService.GetActKPIReq req) {
        Map map = Maps.newHashMap();
        if (req.permit != null && !"".equals(req.permit)) {
            map.put("permit", req.permit);
        }
        if (req.actCode != null && !"".equals(req.actCode)) {
            map.put("actCode", req.actCode);
        }
        if (req.actEndDate != null && !"".equals(req.actEndDate)) {
            map.put("actEndDate", req.actEndDate);
        }
        if (req.actStartDate != null && !"".equals(req.actStartDate)) {
            map.put("actStartDate", req.actStartDate);
        }
        return map;
    }

    @Override
    public List find达成邀约数(MarketService.GetActKPIReq req) {
        String sql = "select * FROM `LSH待办事项`\n" +
                "\tWHERE `许可` =:permit AND `待办分类` = '预约接待' AND `开始时间` >=:actStartDate AND `结束时间` <=:actEndDate AND `附属编号类型` = '线索' AND `附属编号` IN\n" +
                "\t(select `导入编号` FROM `LSH导入名单`\n" +
                "\t\t\tWHERE `导入状态` = '0' AND `是否线索` = '是' AND `许可` =:permit AND `活动编号` =:actCode)";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List find实际出席总数(MarketService.GetActKPIReq req) {
        String sql = "select * from `顾客进店记录表` a \n" +
                "join inroom_act b on a.`进店编号` = b.inroomID \n" +
                "where a.`许可` =:permit and b.actID =:actCode and a.`进店日期` >=:actStartDate and a.`进店日期` <=:actEndDate\n" +
                "GROUP BY a.顾客编号";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List find邀约出席数(MarketService.GetActKPIReq req) {
        String sql = "select a.顾客编号 from `顾客进店记录表` a \n" +
                "join inroom_act b on a.`进店编号` = b.inroomID \n" +
                "join `顾客记录汇总` c on a.`顾客编号` = c.`顾客编号`\n" +
                "join LSH导入名单 d on c.`电话号码` = d.`电话号码`\n" +
                "where a.`许可` =:permit and b.actID =:actCode and a.`进店日期` >=:actStartDate and a.`进店日期` <=:actEndDate AND d.`导入状态` = '0'\n" +
                "GROUP BY a.顾客编号";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List findRecordByParams(MarketService.GetActKPIReq req, String param) {
        String sql = "select * from `顾客进店记录表` a \n" +
                "join inroom_act b on a.`进店编号` = b.inroomID \n" +
                "where a.`许可` =:permit and b.actID =:actCode and a.`进店日期` >=:actStartDate and a.`进店日期` <=:actEndDate and a." + param + " = '是'\n" +
                "GROUP BY a.顾客编号";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List find订单数(MarketService.GetActKPIReq req) {
        String sql = "select * from `顾客进店记录表` a \n" +
                "join inroom_act b on a.`进店编号` = b.inroomID \n" +
                "where a.`许可` =:permit and b.actID =:actCode \n" +
                "and a.`进店日期` >=:actStartDate \n" +
                "and a.`进店日期` <= date_add(:actEndDate, interval + (select c.值 from `itask基础设置` c where c.许可 = 'LSH' and c.设置项目 = '主动集客市场活动延长期') day) \n" +
                "and a.是否订单 = '是'\n" +
                "GROUP BY a.顾客编号";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List find零售数(MarketService.GetActKPIReq req) {
        String sql = "select * from `顾客进店记录表` a \n" +
                "join inroom_act b on a.`进店编号` = b.inroomID \n" +
                "where a.`许可` =:permit and b.actID =:actCode \n" +
                "and a.`进店日期` >=:actStartDate \n" +
                "and a.`进店日期` <= date_add(:actEndDate, interval + (select c.值 from `itask基础设置` c where c.许可 = 'LSH' and c.设置项目 = '主动集客市场活动延长期') day) \n" +
                "and a.是否零售 = '是'\n" +
                "GROUP BY a.顾客编号";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List find新增线索数(MarketService.GetActKPIReq req) {
        String sql = "SELECT * FROM LSH导入名单 WHERE 许可 =:permit AND `活动编号` =:actCode AND 是否线索 = '是' AND 是否有效线索 = '是' AND `导入状态` IS NOT NULL";
        Map map = getKpiPramsMap(req);
        map.remove("actStartDate");
        map.remove("actEndDate");
        return this.getList(sql, map);
    }

    @Override
    public List find线索首次进店数(MarketService.GetActKPIReq req) {
        String sql = "select * from `顾客进店记录表` a \n" +
                "join inroom_act b on a.`进店编号` = b.inroomID \n" +
                "where a.`许可` =:permit and b.actID =:actCode and a.`进店日期` >=:actStartDate and a.`进店日期` <=:actEndDate and a.是否首次到店 = '是'\n" +
                "GROUP BY a.顾客编号";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List find线索再次进店数(MarketService.GetActKPIReq req) {
        String sql = "select * from `顾客进店记录表` a \n" +
                "join inroom_act b on a.`进店编号` = b.inroomID \n" +
                "where a.`许可` =:permit and b.actID =:actCode and a.`进店日期` >=:actStartDate and a.`进店日期` <=:actEndDate and a.是否首次到店 = '否'\n" +
                "GROUP BY a.顾客编号";
        Map map = getKpiPramsMap(req);
        return this.getList(sql, map);
    }

    @Override
    public List findSignFormList(MarketService.GetActKPIReq req) {
        String sql = "SELECT a.`顾客姓名`,c.人员姓名 as `所属销售顾问`,a.`电话号码` " +
                "FROM  `lsh导入名单` a \n" +
                "LEFT JOIN `用户` c ON c.用户名=a.`所属销售顾问` " +
                "JOIN `lsh待办事项` b ON a.导入编号=b.附属编号\n" +
                "WHERE a.导入状态='0' AND a.`是否线索`='是' AND a.许可=:permit AND a.活动编号=:actCode AND b.许可=:permit AND b.`待办分类`='预约接待' \n" +
                "  AND `附属编号类型` = '线索'";
        Map map = getKpiPramsMap(req);
        map.remove("actStartDate");
        map.remove("actEndDate");//按要求取消时间限制 2017-03-22
        return this.getList(sql, map);
    }

    //查询导入名单表中三个月内有效线索
    @Override
    public List findImportListValidClue (String permit, String cdo, String sc, String phone) {
        StringBuffer sql = new StringBuffer();
        Map param = new HashMap();
        sql.append(" SELECT * FROM `lsh导入名单` a " +
                " WHERE a.`是否有效线索` = '是' AND a.`是否线索` = '是' " +
                " AND a.`线索创建日期` >= date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) -1 day),interval 2 month) ");
        if (!StringUtils.isNullOrBlank(permit)) {
            sql.append(" AND a.`许可` = :permit ");
            param.put("permit", permit);
        }
        if (!StringUtils.isNullOrBlank(cdo)) {
            sql.append(" AND a.`所属CDO` = :cdo ");
            param.put("cdo", cdo);
        }
        if (!StringUtils.isNullOrBlank(sc)) {
            sql.append(" AND a.`所属销售顾问` = :sc ");
            param.put("sc", sc);
        }
        if (!StringUtils.isNullOrBlank(permit)) {
            sql.append(" AND a.`电话号码` = :phone ");
            param.put("phone", phone);
        }
        return this.getList(sql.toString(), param);
    }

}
